// dao/userDao.js
// 实现与MySQL交互
var mysql = require('mysql');
var $conf = require('../conf/db');
var $sql = require('./userSqlMapping');

// 使用连接池，提升性能
var pool  = mysql.createPool($conf.mysql);

// 向前台返回JSON方法的简单封装

var jsonWrite2 = function(res, ret){
    if(ret.affectedRows === 0){
         res.json({
            code:'1',
            msg: '操作失败'
        });
    }else{
        res.json({code:'200',result:'success'});
    };
}
var jsonWrite = function (res, ret) {
    if(typeof ret === 'undefined') {
        res.json({
            code:'1',
            msg: '操作失败'
        });
    } else if(ret[0]!=null){
        res.json({code:'200',result:'success'});
    } else{
        res.json({code:'200',result:'null'});
    }
};
var returnJson=function(res,ret){
    res.json({code:'200',result:ret});
};

module.exports = {
    add: function (req, res) {
        pool.getConnection(function(err, connection) {
            // 获取前台页面传过来的参数
            var param = req.body;
            // 建立连接，向表中插入值
            // 'INSERT INTO user(id, name, age) VALUES(0,?,?)',
            connection.query($sql.insert, [param.name,param.password,param.sex,param.address,param.number], function(err, result) {
                if(result) {
                    result = {
                        code: 200,
                        msg:'增加成功'
                    };
                }else{
                    result={
                        code:900,
                        msg:err
                    }
                }

                // 以json形式，把操作结果返回给前台页面
                jsonWrite(res, result);

                // 释放连接
                connection.release();
            });
        });
    },
    delete: function (req, res, next) {
        // delete by Id
        pool.getConnection(function(err, connection) {
            var id = +req.query.id;
            connection.query($sql.delete, id, function(err, result) {
                if(result.affectedRows > 0) {
                    result = {
                        code: 200,
                        msg:'删除成功'
                    };
                } else {
                    result = void 0;
                }
                jsonWrite(res, result);
                connection.release();
            });
        });
    },
    update: function (req, res, next) {
        // update by id
        // 为了简单，要求同时传name和age两个参数
        var param = req.body;
        if(param.name == null || param.age == null || param.id == null) {
            jsonWrite(res, undefined);
            return;
        }

        pool.getConnection(function(err, connection) {
            connection.query($sql.update, [param.name, param.age, +param.id], function(err, result) {
                // 使用页面进行跳转提示
                if(result.affectedRows > 0) {
                    res.render('suc', {
                        result: result
                    }); // 第二个参数可以直接在jade中使用
                } else {
                    res.render('fail',  {
                        result: result
                    });
                }

                connection.release();
            });
        });

    },
    queryById: function (req, res, next) {
        var id = +req.query.id; // 为了拼凑正确的sql语句，这里要转下整数
        pool.getConnection(function(err, connection) {
            connection.query($sql.queryById, id, function(err, result) {
                jsonWrite(res, result);
                connection.release();

            });
        });
    },
    queryUser: function (req, res, next) {
        var num=req.body.num;
        var keyword=req.body.keyword;
        console.log('0'+keyword+'123');
        pool.getConnection(function(err, connection) {
            if(num!='all'){
                var all={};
                connection.query($sql.queryNum,[keyword,keyword,keyword,keyword,keyword,num*20,20], function(err, result) {
                    all.result=result;
                    console.log(result);
                    connection.query($sql.queryCount, [keyword,keyword,keyword,keyword,keyword],function(err, result) {
                        all.count=result[0].count;
                        returnJson(res,all);
                        connection.release();
                    });
                });

            }else{
                connection.query($sql.queryAll, function(err, result) {
                    returnJson(res, result);
                    connection.release();
                });
            }
        });

    },
    login:function(req,res,next){
        var userIf = req.body;
        if(userIf.name == null || userIf.password == null) {
            jsonWrite(res, undefined);
            return;
        }
        pool.getConnection(function(err,connection){
            connection.query($sql.login,[userIf.name,userIf.password],function(err,result){
                jsonWrite(res,result);
                connection.release();
            })
        })
    },
    deleteUser:function(req,res){
        var userID= req.body.id;
        pool.getConnection(function(err,connection){
           connection.query($sql.delete,[userID],function(err,result){
            console.log('@@@@@@@@@@@@@@');
                console.log(result);
                console.log('@@@@@@@@@@@@@@');
                jsonWrite2(res,result);
                connection.release();
            }) 
        })
    },
    randomUser:function(req,res ,next){
        pool.getConnection(function(err,connection){
            for(var i=0;i<8000;i++){
                var param={
                    name:new Date(),
                    password:123456,
                    sex:Math.round(Math.random()),
                    address:['浙江','辽宁','北京','上海','重庆','武汉','湖北'][Math.round(Math.random()*5)],
                    number:13968114520
                };
                connection.query($sql.insert, [param.name,param.password,param.sex,param.address,param.number],function(){
                    console.log(i);
                })
            }
        });
    }
};